# 加载所需使用的模块
from datetime import datetime

from vnpy.trader.datafeed import get_datafeed
from vnpy.trader.database import get_database, DB_TZ
from vnpy.trader.constant import Interval
from vnpy.trader.object import BarData, HistoryRequest
from vnpy.trader.utility import extract_vt_symbol
from vnpy.trader.setting import SETTINGS
import tushare as ts
import pandas as pd
import numpy as np
import pymysql
import os

# 配置数据服务
# SETTINGS["datafeed.name"] = "tushare"            # 可以根据自己的需求选择数据服务：rqdata/xt/wind等
# SETTINGS["datafeed.username"] = "835983"       # RQData的用户名统一为“license”这个字符串
# SETTINGS["datafeed.password"] = "f9070562c743804c9627e56832d30c32a22f2f8c06633357428e6d98"        # 这里需要替换为你购买或者申请试用的RQData数据license

# # 配置数据库
# SETTINGS["database.name"] = "mysql"              # 可以根据自己的需求选择数据库，这里使用的是TDengine
# SETTINGS["database.database"] = "vnpy"
# SETTINGS["database.host"] = "127.0.0.1"
# SETTINGS["database.port"] = 3306
# SETTINGS["database.user"] = "root"
# SETTINGS["database.password"] = "12345678"

# 创建对象实例
# datafeed = get_datafeed()
pro = ts.pro_api("4a53e6dcf27e7dc0875bc09061943dc99f968637084495a9a36732b6")        #李处lichess的token

# database = get_database()
# conn = pymysql.connect(
#             host="127.0.0.1",
#             port=3306,
#             user="root",
#             password="12345678",
#             database="vnpy",
#             charset='utf8mb4',
#             cursorclass=pymysql.cursors.DictCursor
#         )
# loadBasicStockSql = """select * from stock_basic where ts_code like '%BJ' or ts_code like '6%';"""
# cursor = conn.cursor()
# cursor.execute(loadBasicStockSql)
# results = cursor.fetchall()




# 要下载数据的合约代码
# vt_symbols = [
#     "IF2501.CFFEX",
#     "IF2502.CFFEX",
#     "IF2503.CFFEX",

#     "IH2501.CFFEX",
#     "IH2502.CFFEX",
#     "IH2503.CFFEX",

#     "IC2501.CFFEX",
#     "IC2502.CFFEX",
#     "IC2503.CFFEX",

#     "IM2501.CFFEX",
#     "IM2502.CFFEX",
#     "IM2503.CFFEX",
# ]
# ts_code_lists = [result['ts_code'] for result in results]
# #ts_code_list实现replace("SH", "SSE").replace("SZ", "SZSE")
# vt_symbols = [vt_symbol.replace("SH", "SSE").replace("SZ", "SZSE").replace("BJ", "BSE") for vt_symbol in ts_code_lists]


# print(vt_symbols)

# 遍历列表执行下载
# for vt_symbol in vt_symbols:
#     # 拆分合约代码和交易所
#     symbol, exchange = extract_vt_symbol(vt_symbol)

#     # 创建历史数据请求对象
#     req: HistoryRequest = HistoryRequest(
#         symbol=symbol,
#         exchange=exchange,
#         start=start,
#         end=end,
#         interval=Interval.DAILY        # 这里下载最常用的1分钟K线
#     )

#     # 从数据服务下载数据
#     bars: list[BarData] = datafeed.query_bar_history(req)

#     # 如果下载成功则保存
#     if bars:
#         database.save_bar_data(bars)
#         print(f"下载数据成功：{vt_symbol}，总数据量：{len(bars)}")
#     # 否则失败则打印信息
#     else:
#         print(f"下载数据失败：{vt_symbol}")
    
#     print("全部下载完成")
#     updateEndTimeSql = """ update dbbaroverview set end = now() where 1=1"""
#     #更新时间为最新更新时间

#     cursor.execute(updateEndTimeSql)
#     print("更新数据库时间成功")
#     conn.commit()
#     conn.close()
current_working_dir = os.getcwd()
data_dir = current_working_dir + "/data/tushare"
pro = ts.pro_api() 
# E 表示场内，O 表示场外
eft = pro.fund_basic(market='E')

eft.to_csv(os.path.join(data_dir, "etf_fund_basic2.csv"), index=False, encoding="utf_8_sig")
print("场内基金列表数据etf_fund_basic.csv 保存成功")

# df = df.where(pd.notnull(df), None)
# df = df.replace({np.nan: None})
# updateFundSql = """ INSERT INTO fund_basic (
#     ts_code, name, management, custodian, fund_type, found_date, due_date,
#     list_date, issue_date, delist_date, issue_amount, m_fee, c_fee,
#     duration_year, p_value, min_amount, exp_return, benchmark, status,
#     invest_type, type, trustee, purc_startdate, redm_startdate, market
# ) VALUES (
#     %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
#     %s, %s, %s, %s, %s, %s, %s, %s, %s
# ) """
# data_to_insert = [tuple(item.values()) for item in df.to_dict(orient='records')]
# print(data_to_insert)

# try:
#     with conn.cursor() as cursor:
#         cursor.executemany(updateFundSql, data_to_insert)
#     conn.commit()
#     print(f"成功插入 {len(df)} 条数据")
# except Exception as e:
#     conn.rollback()
#     print(f"插入失败: {e}")
# finally:
#     conn.close()